/********************************************************
 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
 * Written by Robert Simpson (robert@blackcastlesoft.com)
 * 
 * Released to the public domain, use at your own risk!
 ********************************************************/

namespace EffiProz.Designer.Design
{
  using System;
  using System.Data.Common;
  using System.ComponentModel.Design;
  using System.ComponentModel;
  using System.Drawing.Design;
  using System.Collections.Generic;
  using System.Data;
  using System.Text;
  using System.Globalization;
  using EffiProz.Designer.Editors;

  internal abstract class ViewTableBase: IHaveConnection
  {
    public abstract string OldName { get; }
    public abstract string Name { get; set; }
    public abstract string Catalog { get; }
    public abstract string Schema { get; }
    //public abstract object Triggers { get; }
    public abstract void MakeDirty();
    public abstract DbConnection GetConnection();
    public abstract ViewTableBase DesignTable { get; }
  }

  internal class Table : ViewTableBase, ICustomTypeDescriptor
  {
    private string _name;
    private string _oldname;
    private string _catalog;
    private string  _schema;
    private List<Column> _columns = new List<Column>();
    internal bool _exists;
    private string _origSql = String.Empty;
    private List<Index> _indexes = new List<Index>();
    private List<Index> _oldindexes = new List<Index>();
    private List<ForeignKey> _fkeys = new List<ForeignKey>();
    private List<ForeignKey> _oldfkeys = new List<ForeignKey>();
    private List<string> _check = new List<string>();   
    //private List<Trigger> _triggers = new List<Trigger>();
    //private List<Trigger> _oldtriggers = new List<Trigger>();
    private PrimaryKey _key;
    private PrimaryKey _oldkey;
    internal TableDesignerDoc _owner;
    internal DbConnection _connection;

    internal Table(string tableName, DbConnection connection, TableDesignerDoc owner)
        : this( tableName, null, connection, owner)
    {
    }

    internal Table(string tableName,string schema, DbConnection connection, TableDesignerDoc owner)
    {
      _owner = owner;
      _oldname = tableName;
      _connection = connection;
      _name = tableName;
      _owner.Name = _name;
      _catalog = "PUBLIC"; // main
      _schema = schema ==null ? "PUBLIC" : schema;

      ReloadDefinition();

      if (_key == null) _key = new PrimaryKey(_connection, this, null);

      if (_exists)
      {
          using (DataTable tbl = connection.GetSchema("ForeignKeyColumns", new string[] { null, _schema, Name }))
          {
              foreach (DataRow row in tbl.Rows)
              {
                  _fkeys.Add(new ForeignKey(connection, this, row));
                  _oldfkeys.Add(new ForeignKey(connection, this, row));
              }
          }
      }

      using (DataTable tbl = connection.GetSchema("Columns", new string[] { _catalog, _schema, Name }))
      {
        foreach (DataRow row in tbl.Rows)
        {
          _columns.Add(new Column(row, this));
        }
      }

      if (_exists)
      {
          _origSql = GetSql();
      }
    }

    public bool TableExists
    {
        get
        {
            return _exists;
        }
    }

    public override void MakeDirty()
    {
      _owner.MakeDirty();
    }

    private void ReloadDefinition()
    {
        using (DataTable tbl = _connection.GetSchema("Tables", new string[] { _catalog, _schema, Name }))
        {
            if (tbl.Rows.Count > 0)
            {
                _exists = true;
                _oldname = Name;
                _origSql = " ";
            }
            else
            {
                _exists = false;
                return;
            }
        }
      
      _indexes.Clear();
      _oldindexes.Clear();

      using (DataTable tbl = _connection.GetSchema("Indexes", new string[] { _catalog, _schema, Name }))
      {
          foreach (DataRow row in tbl.Rows)
          {           
             _indexes.Add(new Index(_connection, this, row));
             _oldindexes.Add(new Index(_connection, this, row));
                 
          }
      }

      using (DataTable tbl = _connection.GetSchema("PrimaryKeys", new string[] { _catalog, _schema, Name }))
      {
          if (tbl.Rows.Count >0)
          {
              _key = new PrimaryKey(_connection, this, tbl.Rows[0]);
              _oldkey = new PrimaryKey(_connection, this, tbl.Rows[0]);              
          }
      }     
      

      //_triggers.Clear();
      //_oldtriggers.Clear();

      //using (DataTable tbl = _connection.GetSchema("Triggers", new string[] { null, _schema, Name }))
      //{
      //  foreach (DataRow row in tbl.Rows)
      //  {
      //    Trigger t = new Trigger(this, row);
      //    _triggers.Add(t);
      //    _oldtriggers.Add(((ICloneable)t).Clone() as Trigger);

      //    builder.AppendFormat("{0};\r\n", t.OriginalSql);
      //  }
      //}

     
    }

    internal void Committed()
    {
      _exists = true;
      ReloadDefinition();

      foreach (Column c in Columns)
        c.Committed();

      foreach (ForeignKey key in ForeignKeys)
        key.ClearDirty();

      foreach (Index idx in Indexes)
        idx.ClearDirty();

      if (PrimaryKey != null)
        PrimaryKey.ClearDirty();
    }

    [Browsable(false)]
    public List<Index> Indexes
    {
      get { return _indexes; }
    }

    [Browsable(false)]
    public PrimaryKey PrimaryKey
    {
      get { return _key; }
      set
      {
        _key = value;
        _owner.Invalidate();
      }
    }

    [Browsable(false)]
    public List<ForeignKey> ForeignKeys
    {
      get { return _fkeys; }
    }

    [Browsable(false)]
    public List<string> Check
    {
      get { return _check; }
    }

    //[Browsable(false)]
    //public override object Triggers
    //{
    //  get { return _triggers; }
    //}

    [Browsable(false)]
    public string OriginalSql
    {
      get { return _origSql; }
    }

    [Category("Storage")]
    [RefreshProperties(RefreshProperties.All)]
    [ParenthesizePropertyName(true)]
    [NotifyParentProperty(true)]
    public override string Name
    {
      get { return _name; }
      set
      {
        if (_name != value)
        {
          _name = value;
          _owner.Name = value;
          _owner.MakeDirty();
        }
      }
    }

    [Browsable(false)]
    public override string OldName
    {
      get { return _oldname; }
    }

    public override string ToString()
    {
      return String.Format(CultureInfo.InvariantCulture, "[{0}].[{1}]", Catalog, Name);
    }

    [Category("Storage")]
    [Editor(typeof(CatalogTypeEditor), typeof(UITypeEditor))]
    [DefaultValue("public")]
    [RefreshProperties(RefreshProperties.All)]
    public override string Catalog
    {
      get { return _catalog; }
    }

    [Category("Storage")]
    [Editor(typeof(CatalogTypeEditor), typeof(UITypeEditor))]
    [DefaultValue("public")]
    [RefreshProperties(RefreshProperties.All)]
    public override string Schema
    {
        get { return _schema; }
    }

    [Category("Storage")]
    public string Database
    {
      get { return _connection.Database; }
    }

    [Browsable(false)]
    public List<Column> Columns
    {
      get { return _columns; }
    }

  

    public List<string> GetSqlList()
    {
        StringBuilder builder = null;
        List<string> sqlList = new List<string>();
  
        string altName = null;

        if (_exists)
        {
            Guid g = Guid.NewGuid();
            altName = String.Format(CultureInfo.InvariantCulture, "{0}_{1}", Name, g.ToString("N"));

            if (_oldindexes.Count > 0)
            {
                //builder.Append("-- Drop previous pk constraints on the table\r\n");
                if (_oldkey != null && _oldkey.Columns.Count > 0)
                {
                    sqlList.Add(String.Format("ALTER TABLE {0}.{1} DROP CONSTRAINT {0}.{2};\r\n",
                        _schema, _oldname, _oldkey.Name));
                }

                //builder.Append("-- Drop previous fk constraints on the table\r\n");
                foreach (var fkey in _oldfkeys)
                {
                    sqlList.Add(String.Format("ALTER TABLE {0}.{1} DROP CONSTRAINT {0}.{2};\r\n",
                        _schema, _oldname, fkey.Name));
                }

                //builder.Append("-- Drop previous indexes on the table\r\n");
                foreach (Index idx in _oldindexes)
                {
                    if (!idx.Name.StartsWith("SYS_"))
                        sqlList.Add(String.Format("DROP INDEX {0}.{1} IF EXISTS;\r\n",
                            _schema, idx.Name));
                }
            }

            foreach (Column c in Columns)
            {
                if(c.IsDirty)
                  c.GetAlterSql(sqlList);
            }

        }
        else
        {
            _oldname = Name;
            builder = new StringBuilder();
            builder.AppendFormat("CREATE TABLE {0}.{1} (\r\n", _schema, Name);
            string separator = "    ";

            foreach (Column c in Columns)
            {
                if (String.IsNullOrEmpty(c.ColumnName) == false)
                {
                    builder.Append(separator);
                    c.WriteSql(builder);
                    separator = ",\r\n    ";
                }
            }
            builder.Append(");");

            sqlList.Add(builder.ToString());
        }
            

        if (_key.Columns.Count > 0)
        {
            string innersep = "";
            builder = new StringBuilder();
            builder.AppendFormat(CultureInfo.InvariantCulture,
                "ALTER TABLE {0}.{1} ADD CONSTRAINT {2} PRIMARY KEY (", _schema, _oldname, _key.Name);
            foreach (IndexColumn c in _key.Columns)
            {
                builder.AppendFormat(CultureInfo.InvariantCulture, "{0}{1}", innersep, c.Column);

                innersep = ", ";
            }
            builder.Append(");");

            sqlList.Add(builder.ToString());
        }

        List<ForeignKey> keys = new List<ForeignKey>();

        for (int x = 0; x < ForeignKeys.Count; x++)
        {
            ForeignKey key = ForeignKeys[x];

            if (String.IsNullOrEmpty(key.From.Column) == true || String.IsNullOrEmpty(key.From.Schema) == true ||
              String.IsNullOrEmpty(key.To.Table) == true || String.IsNullOrEmpty(key.To.Column) == true)
                continue;

            if (keys.Count > 0)
            {
                if (keys[0].Name == key.Name && keys[0].To.Schema == key.To.Schema && keys[0].To.Table == key.To.Table)
                {
                    keys.Add(key);
                    continue;
                }

                builder = new StringBuilder();
                WriteFKeys(keys, builder);
                sqlList.Add(builder.ToString());
                keys.Clear();
            }
            keys.Add(key);
        }

        if (keys.Count > 0)
        {
            builder = new StringBuilder();
            WriteFKeys(keys, builder);
            sqlList.Add(builder.ToString());
        }    
      

        
        if (_indexes.Count > 0)
        {
            //builder.Append("\r\n-- Create the new indexes");
            foreach (Index idx in _indexes)
            {
                if (idx.Name.StartsWith("SYS_"))
                    continue;
                builder = new StringBuilder();               
                idx.WriteSql(builder);
                sqlList.Add(builder.ToString());
            }         
        }
       
        return sqlList;
    }


    public string GetSql()
    {
      StringBuilder builder = new StringBuilder();
      List<String> sqlList = GetSqlList();

      foreach (var item in sqlList)
      {
          builder.Append(item).Append("\r\n");
      }

      return builder.ToString();
    }

    private void WriteFKeys(List<ForeignKey> keys, StringBuilder builder)
    {

        builder.AppendFormat("ALTER TABLE {0}.{1} ADD CONSTRAINT {2} FOREIGN KEY (",_schema,_oldname, keys[0].Name);
      string separator = "";

      foreach (ForeignKey key in keys)
      {
          builder.AppendFormat("{0}{1}", separator, key.From.Column);
        separator = ", ";
      }

      builder.AppendFormat(") REFERENCES {0}.{1} (", keys[0].To.Schema, keys[0].To.Table);

      separator = "";
      foreach (ForeignKey key in keys)
      {
          builder.AppendFormat("{0}{1}", separator, key.To.Column);
        separator = ", ";
      }
      builder.Append(")");
    }

    [Browsable(false)]
    public override ViewTableBase DesignTable
    {
      get { return this; }
    }

    public override DbConnection GetConnection()
    {
      return _connection;
    }

    #region ICustomTypeDescriptor Members

    AttributeCollection ICustomTypeDescriptor.GetAttributes()
    {
      return TypeDescriptor.GetAttributes(GetType());
    }

    string ICustomTypeDescriptor.GetClassName()
    {
      return "Table Design";
    }

    string ICustomTypeDescriptor.GetComponentName()
    {
      return ToString();
    }

    TypeConverter ICustomTypeDescriptor.GetConverter()
    {
      return TypeDescriptor.GetConverter(GetType());
    }

    EventDescriptor ICustomTypeDescriptor.GetDefaultEvent()
    {
      return TypeDescriptor.GetDefaultEvent(GetType());
    }

    PropertyDescriptor ICustomTypeDescriptor.GetDefaultProperty()
    {
      return TypeDescriptor.GetDefaultProperty(GetType());
    }

    object ICustomTypeDescriptor.GetEditor(Type editorBaseType)
    {
      return TypeDescriptor.GetEditor(GetType(), editorBaseType);
    }

    EventDescriptorCollection ICustomTypeDescriptor.GetEvents(Attribute[] attributes)
    {
      return TypeDescriptor.GetEvents(GetType(), attributes);
    }

    EventDescriptorCollection ICustomTypeDescriptor.GetEvents()
    {
      return TypeDescriptor.GetEvents(GetType());
    }

    PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties(Attribute[] attributes)
    {
      return TypeDescriptor.GetProperties(GetType(), attributes);
    }

    PropertyDescriptorCollection ICustomTypeDescriptor.GetProperties()
    {
      return TypeDescriptor.GetProperties(GetType());
    }

    object ICustomTypeDescriptor.GetPropertyOwner(PropertyDescriptor pd)
    {
      return this;
    }

    #endregion
  }

  internal interface IHaveConnection
  {
    DbConnection GetConnection();
    [Browsable(false)]
    ViewTableBase DesignTable { get; }
  }

  internal interface IHaveConnectionScope : IHaveConnection
  {
    [Browsable(false)]
    string CatalogScope { get; }
    [Browsable(false)]
    string TableScope { get; }
  }

  internal class CollationTypeEditor : ObjectSelectorEditor
  {
    public override bool IsDropDownResizable
    {
      get
      {
        return true;
      }
    }

    public override UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext context)
    {
      return UITypeEditorEditStyle.DropDown;
    }

    protected override void FillTreeWithData(Selector selector, ITypeDescriptorContext context, IServiceProvider provider)
    {
      base.FillTreeWithData(selector, context, provider);
      selector.AddNode("BINARY", "BINARY", null);
      selector.AddNode("NOCASE", "NOCASE", null);
    }
  }

  internal class CatalogTypeEditor : ObjectSelectorEditor
  {
    public override bool IsDropDownResizable
    {
      get
      {
        return true;
      }
    }

    public override UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext context)
    {
      return UITypeEditorEditStyle.DropDown;
    }

    protected override void FillTreeWithData(Selector selector, ITypeDescriptorContext context, IServiceProvider provider)
    {
      base.FillTreeWithData(selector, context, provider);
      IHaveConnection source = context.Instance as IHaveConnection;

      if (source == null) return;

      using (DataTable table = source.GetConnection().GetSchema("Catalogs"))
      {
        foreach (DataRow row in table.Rows)
        {
          selector.AddNode(row[0].ToString(), row[0], null);
        }
      }
    }
  }
}
